package com.sec.etech.form.cust.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.util.AddAliasesVisitor;
import org.apache.commons.lang3.StringUtils;
import org.openbpm.base.api.response.impl.ResultMsg;
import org.openbpm.base.core.util.BeanUtils;
import org.openbpm.base.core.util.ExceptionUtil;
import org.openbpm.base.core.util.StringUtil;
import org.openbpm.form.util.ArrayUtil;
import org.openbpm.form.util.JSONObjectUtil;
import org.openbpm.form.util.SqlInjectionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import java.text.MessageFormat;
import java.util.*;

/**
 * update jsqlparser >v2.0  v3.1
 */

public class SqlUtil {
    protected static final Logger log = LoggerFactory.getLogger(org.openbpm.form.util.SqlUtil.class);


    public static final String DATABSE_TYPE_MYSQL = "mysql";


    public static final String DATABSE_TYPE_POSTGRE = "postgresql";


    public static final String DATABSE_TYPE_ORACLE = "oracle";


    public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";


    public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}";


    public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";


    public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}";


    public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}";


    public static final String MYSQL_ALLTABLES_SQL = "select distinct table_name from information_schema.columns where table_schema = {0}";


    public static final String POSTGRE__ALLTABLES_SQL = "SELECT distinct c.relname AS  table_name FROM pg_class c";


    public static final String ORACLE__ALLTABLES_SQL = "select distinct colstable.table_name as  table_name from user_tab_cols colstable";


    public static final String SQLSERVER__ALLTABLES_SQL = "select distinct c.name as  table_name from sys.objects c";


    public static final String MYSQL_ALLCOLUMNS_SQL = "select column_name from information_schema.columns where table_name = {0} and table_schema = {1}";


    public static final String POSTGRE_ALLCOLUMNS_SQL = "select table_name from information_schema.columns where table_name = {0}";


    public static final String ORACLE_ALLCOLUMNS_SQL = "select column_name from all_tab_columns where table_name ={0}";

    public static final String SQLSERVER_ALLCOLUMNS_SQL = "select name from syscolumns where id={0}";


    public static String getFullSql(String sql, Map params) {
        StringBuilder sqlB = new StringBuilder();
        sqlB.append("SELECT t.* FROM ( ");
        sqlB.append(sql + " ");
        sqlB.append(") t ");
        if (params != null && params.size() >= 1) {
            sqlB.append("WHERE 1=1  ");
            Iterator it = params.keySet().iterator();
            while (it.hasNext()) {
                String key = String.valueOf(it.next());
                String value = String.valueOf(params.get(key));
                if (!StringUtil.isEmpty(value) && !"null".equals(value)) {
                    sqlB.append(" AND ");
                    sqlB.append(" " + key + value);
                }
            }
        }
        return sqlB.toString();
    }


    public static String getCountSql(String sql, Map params) {
        String querySql = getFullSql(sql, params);
        querySql = "SELECT COUNT(*) FROM (" + querySql + ") t2";
        return querySql;
    }


    public static String getAddupSql(String sql, String[] fields) {
        String sumFields = "";
        for (int i = 0; i < fields.length; i++) {
            if (i < fields.length - 1) {
                sumFields = sumFields + " sum(" + fields[i].trim() + ") as " + fields[i].trim() + ",";
            } else {
                sumFields = sumFields + " sum(" + fields[i].trim() + ") as " + fields[i].trim() + " ";
            }
        }
        String addupSql = "SELECT " + sumFields + " FROM (" + sql + ") t2";
        return addupSql;
    }

    public static String getSumSql(String sumSqlColumn, String sql) {

        // 可能的优化，select中去掉sum统计之外的字段

        String result = "";
        String[] sumFields = null;
        Set<String> setSumFileds = new HashSet<>();
        if(StringUtil.isNotEmpty(sumSqlColumn)){
            sumFields = sumSqlColumn.split(",");
            for(String field: sumFields){
                setSumFileds.add(field.replaceAll("`", "").trim());
            }
        }
        try {
            Select select = (Select) CCJSqlParserUtil.parse(sql);
            // bug fix
            SelectBody selectBody = select.getSelectBody();
            //PlainSelect/SetOperationList/WithItem
            if (selectBody instanceof PlainSelect) {
                // continue
            } else if (selectBody instanceof SetOperationList) {
                SetOperationList setOperationList = (SetOperationList) selectBody;
                return getSimpleSumSql(sumFields, sql);
            } else if (selectBody instanceof WithItem) {
                return getSimpleSumSql(sumFields, sql);
            } else {
                return getSimpleSumSql(sumFields, sql);
            }

            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            List<SelectItem> selectItems = plainSelect.getSelectItems();
            if (CollectionUtils.isEmpty(selectItems)) {
                return getSimpleSumSql(sumFields, sql);
            }



            Limit limit = plainSelect.getLimit();
            if (limit != null) {
                plainSelect.setLimit(null);
            }
            List<String> fieldList = new ArrayList<>();
            Iterator<SelectItem> iterator = selectItems.iterator();
            boolean hasColumn = false;
            while (iterator.hasNext()) {
                SelectItem next = iterator.next();
                if (next instanceof net.sf.jsqlparser.statement.select.AllColumns
                        || next instanceof net.sf.jsqlparser.statement.select.AllTableColumns) {
                    continue;
                }

                SelectExpressionItem selectItem = (SelectExpressionItem) next;
                Expression expression = selectItem.getExpression();
                String field = "";
                Alias alias = selectItem.getAlias();

                // 存在别名的统计字段, 不管哪种形式都需要添加
                if(alias!=null){
                    field = alias.getName().replaceAll("`", "");
                    if(setSumFileds.contains(field)){
                        hasColumn = true;
                        fieldList.add(field);
                        continue;
                    }
                }

                // 统计字段，普通字段无别名
                if (expression instanceof Column){
                    field = ((Column) expression).getColumnName().replaceAll("`", "");
                    if(setSumFileds.contains(field)){
                        hasColumn = true;
                        fieldList.add(field);
                        continue;
                    }
                }
                // 增加 函数function匹配
                // TODO 2022-7-11 影响查询，可以优化, 没搞懂为什么需要!
                if (expression instanceof net.sf.jsqlparser.expression.Function
                        || expression instanceof net.sf.jsqlparser.statement.select.SubSelect) {
                    hasColumn = true;
                    field = alias.getName().replaceAll("`", "");
                    fieldList.add(field);
                    continue;
                }

                if (!hasColumn) {
                    hasColumn = true;
                    if (alias != null) {
                        field = alias.getName().replaceAll("`", "");
                    } else {
                        field = ((Column) expression).getColumnName();
                    }
                    fieldList.add(field);
                }
            }

            plainSelect.setOrderByElements(new ArrayList());

            if (fieldList.size() == 0) {

                plainSelect.setSelectItems(getSumItem(sumFields));
                sql = select.toString();
                if (hasGroupByExp(sql)) {
                    sql = getSimpleSumSql(sumFields, sql);
                }
                return sql;
            }

            // TODO
            String outWhere = getNoOutWhereSql(select, ArrayUtil.toStringArray(fieldList));

            String sumSql = "";
            if (StringUtils.isEmpty(outWhere)) {
                // TODO 需要获取SUM字段的原始字段名和别名
                // 优化处理： 1 检查sum字段无函数和子查询，重新构造sum(原字段) as sum_[字段别名]
                sql = select.toString();
                sumSql = getSimpleSumSql(sumFields, sql);
//                plainSelect.setSelectItems(getSumItem(sumFields));
//                sumSql = select.toString();
//                if (hasGroupByExp(sumSql)) {
//                    sumSql = getSimpleSumSql(sumFields, sumSql);
//                }
            } else {
                sql = select.toString();
                sumSql = getSimpleSumSql(sumFields, sql) + " where 1=1 " + outWhere;
            }

            return sumSql;
        } catch (JSQLParserException e) {
            log.warn("获取Sum SQL时出现异常, sql:{} , e: {}", sql, e);
            result = getSimpleSumSql(sumFields, sql);

            return result;
        }
    }

    public static String getSimpleSumSql(String[] sumFields, String sql) {
        String result = null;
        if(sumFields==null || sumFields.length==0){
            result = String.format("select count(*) counts from (%s) tmp", new Object[]{sql.toString()});
        }else {
            result = "select count(*) counts ";
            for(String sumField: sumFields){
                result += ", sum("+sumField+") as sum_"+sumField.replaceAll("`", "").trim();
            }
            result += String.format(" from (%s) tmp", new Object[]{sql.toString()});
        }
        return result;
    }

    private static List<SelectItem> getSumItem(String[] sumFields) {
        List<SelectItem> items = new ArrayList<>();

        SelectExpressionItem countItem = new SelectExpressionItem();
        setFunction(countItem);
        countItem.setAlias(new Alias("counts"));
        items.add(countItem);
        if(sumFields!=null && sumFields.length>0){
            for(String sumField: sumFields){
                SelectExpressionItem sumItem = new SelectExpressionItem();
                setSumFunction(sumItem, sumField);
                sumItem.setAlias(new Alias("sum_"+sumField.replaceAll("`", "").trim()));
                items.add(sumItem);
            }
        }
        return items;
    }
    private static void setSumFunction(SelectExpressionItem selectItem, String sumFiled) {
        Function function = new Function();
        function.setName("SUM");
        List<Expression> list = new ArrayList<Expression>();
        list.add(new Column(sumFiled));
        ExpressionList expressionList = new ExpressionList();
        expressionList.setExpressions(list);
        function.setParameters(expressionList);
        selectItem.setExpression((Expression) function);
    }

    public static String getCountSql(String sql) {
        String result = "";

        try {
            Select select = (Select) CCJSqlParserUtil.parse(sql);
            // bug fix
            SelectBody selectBody = select.getSelectBody();
            //PlainSelect/SetOperationList/WithItem
            if (selectBody instanceof PlainSelect) {
                // continue
            } else if (selectBody instanceof SetOperationList) {
                SetOperationList setOperationList = (SetOperationList) selectBody;
                return getSimpleCountSql(sql);
            } else if (selectBody instanceof WithItem) {
                return getSimpleCountSql(sql);
            } else {
                return getSimpleCountSql(sql);
            }

            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            List<SelectItem> selectItems = plainSelect.getSelectItems();
            if (CollectionUtils.isEmpty(selectItems)) {
                return getSimpleCountSql(sql);
            }

            Limit limit = plainSelect.getLimit();
            if (limit != null) {
                plainSelect.setLimit(null);
            }
            List<String> fieldList = new ArrayList<>();
            Iterator<SelectItem> iterator = selectItems.iterator();
            boolean hasColumn = false;
            while (iterator.hasNext()) {
                //hasColumn = false;    // fix 组合字段查询
                SelectItem next = iterator.next();
                if (next instanceof net.sf.jsqlparser.statement.select.AllColumns
                        || next instanceof net.sf.jsqlparser.statement.select.AllTableColumns) {
                    continue;
                }

                SelectExpressionItem selectItem = (SelectExpressionItem) next;
                Expression expression = selectItem.getExpression();
                String field = "";
                Alias alias = selectItem.getAlias();
                if (expression instanceof net.sf.jsqlparser.statement.select.SubSelect) {
                    hasColumn = true;
                    field = alias.getName().replaceAll("`", "");
                    fieldList.add(field);
                    continue;
                }else if(expression instanceof Function){
                    hasColumn = true;
                    field = alias.getName().replaceAll("`", "");
                    fieldList.add(field);
                    continue;
                }
                if (!hasColumn) {
                    hasColumn = true;
                    if (alias != null) {
                        field = alias.getName().replaceAll("`", "");
                    } else {
                        field = ((Column) expression).getColumnName();
                    }
                    fieldList.add(field);
                }
            }


            plainSelect.setOrderByElements(new ArrayList());


            if (fieldList.size() == 0) {


                plainSelect.setSelectItems(getCountItem());
                sql = select.toString();
                if (hasGroupByExp(sql)) {
                    sql = "select count(1) as counts from (" + sql + ") B";
                }
                return sql;
            }


            String outWhere = getNoOutWhereSql(select, ArrayUtil.toStringArray(fieldList));

            String countSql = "";
            if (StringUtils.isEmpty(outWhere)) {
                plainSelect.setSelectItems(getCountItem());
                countSql = select.toString();

                if (hasGroupByExp(countSql)) {
                    countSql = "select count(1) as counts from (" + countSql + ") B";
                }
            } else {
                sql = select.toString();
                countSql = "select count(1) as counts from (" + sql + ") B where 1=1  " + outWhere;
            }

            return countSql;
        } catch (JSQLParserException e) {
            log.warn("获取Count SQL时出现异常, sql:{} , e: {}", sql, e.getMessage());
            result = getSimpleCountSql(sql);

            return result;
        }
    }

    public static String getToDoCountSql(String sql, String pkName, String bpmInstField) {
        try {
            Select select = (Select) CCJSqlParserUtil.parse(sql);
            SelectBody selectBody = select.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
                PlainSelect taskSelect = (PlainSelect) ((SubSelect) plainSelect.getFromItem()).getSelectBody();
                PlainSelect busSelect = (PlainSelect) ((SubSelect) plainSelect.getJoins().get(0).getRightItem()).getSelectBody();

                if (bpmInstField == null) {
                    reduceSelect(taskSelect, "biz_id_");
                    reduceSelect(busSelect, pkName);
                } else {
                    reduceSelect(taskSelect, "inst_id_");
                    reduceSelect(busSelect, bpmInstField);
                }

                return select.toString();
            }

            log.error("error in getToDoCountSql: selectBody is not PlainSelect sql=[{}]", sql);
            return sql;


        } catch (Exception e) {
            log.error("error in getToDoCountSql: sql=[{}] , e=[{}]", sql, e.getMessage());
            return sql;
        }
    }


    public static void reduceSelect(PlainSelect plainSelect, String field) {
        List<SelectItem> selectItemList = plainSelect.getSelectItems();
        List<SelectItem> remainItemList = new ArrayList<>();

        List<String> remainFieldList = new ArrayList<String>(Arrays.asList("task_id_", "plandate", "remain_days_"));
        remainFieldList.add(field);

        for (SelectItem item : selectItemList) {
            for (String remainField : remainFieldList) {
                if (item.toString().contains(remainField)) {
                    remainItemList.add(item);
                }
            }
        }

        plainSelect.setSelectItems(remainItemList);

    }


    public static String sqlReportUpgradeSql(String realSqlColumn, String sql, Map<String, Object> paramData, Map<String, String> columnTableMap, int page, int rows) {
        List<Expression> groupByExp = getGroupByExps(sql);


        if (!CollectionUtils.isEmpty(groupByExp)) {
            sql = removeGroupByExp(sql);
        }


        List<OrderByElement> orderByExp = getOrderByExp(sql);


        if (!CollectionUtils.isEmpty(orderByExp)) {
            sql = removeOrderByExp(sql);
        }


        sql = addWhere(sql, paramData, columnTableMap);


        sql = addGroupByExp(sql, groupByExp);


        sql = addOrderByExp(sql, orderByExp);

        sql = getQueryDataSql(realSqlColumn, sql, page, rows);

        return sql;
    }


    public static String getQueryDataSql(String realSqlColumn, String sql, int curPage, int pageSize) {
        int offset = (curPage - 1) * pageSize;

        String limit = " limit " + offset + "," + pageSize;

        String dataSql = sql + limit;
        String[] realSqlColumnArr = realSqlColumn.split(", ");
        dataSql = optimizeSql(dataSql, realSqlColumnArr);

        return dataSql;
    }


    public static String addGroupByExp(String sql, List<Expression> groupByExp) {
        try {
            if (groupByExp == null || groupByExp.isEmpty()) {
                return sql;
            }
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) selectBody;
                // jsql v3.1
                //plainSelect.setGroupByColumnReferences(groupByExp);
                GroupByElement groupBy = new GroupByElement();
                for (Expression exp : groupByExp) {
                    groupBy.addGroupByExpression(exp);
                }
                plainSelect.setGroupByElement(groupBy);
                return selectStatement.toString();
            }
        } catch (JSQLParserException e) {
            log.error("加回group by异常 : sql : {} ,e:{}", sql, e);
            return sql;
        }
        return sql;
    }


    public static String removeGroupByExp(String sql) {
        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {

                PlainSelect plainSelect = (PlainSelect) selectBody;
                GroupByElement orderByElements = plainSelect.getGroupBy();
                if (orderByElements != null) {
                    plainSelect.setGroupByElement(null);
                    return selectStatement.toString();
                }
                return sql;
            }
        } catch (JSQLParserException e) {
            log.error("移除group by异常 : sql : {} ,e:{}", sql, e);
            return sql;
        }

        return sql;
    }


    private static String addWhere(String sql, Map<String, Object> paramData, Map<String, String> columnTableMap) {
        boolean hasWhereExp = hasWhereExp(sql);
        if (paramData != null) {
            if (!hasWhereExp) {
                sql = sql + " where 1=1 ";
            }
            Set<String> keySet = paramData.keySet();
            for (String key : keySet) {

                String condition = " = ";

                Object value = paramData.get(key);
                if (!StringUtil.isNotEmpty(value)) {
                    continue;
                }
                String column = columnTableMap.get(key);
                if (StringUtil.isEmpty(column)) {
                    if (key.endsWith("_begin")) {
                        key = key.replace("_begin", "");
                        condition = " >= ";
                    } else if (key.endsWith("_end")) {
                        key = key.replace("_end", "");
                        condition = " <= ";
                    } else if (key.startsWith("like_")) {
                        key = key.replace("like_", "");
                        condition = " LIKE ";
                    } else if (key.startsWith("in_")) {
                        key = key.replace("in_", "");
                        condition = " IN ";
                    } else {
                        log.info("找不到字段" + key);
                        continue;
                    }
                    column = columnTableMap.get(key);
                }


                SqlInjectionUtil.filterContent(value.toString());

                if (StringUtils.equals(condition, " LIKE ")) {

                    sql = sql + " AND " + column + condition + " '%" + value + "%'";
                    continue;
                }
                if (StringUtils.equals(condition, " IN ")) {
                    value = ArrayUtil.join((Object[]) value.toString().split(","), "','");
                    sql = sql + " AND " + column + condition + " ('" + value + "')";
                    continue;
                }
                sql = sql + " AND " + column + condition + " '" + value + "'";
            }
        }


        return sql;
    }

    private static int getAfterSelectInsertPoint(String sql) {
        int selectIndex = sql.toLowerCase().indexOf("select");
        int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
        return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);
    }

    public static String getAllTableSql(String dbType, String... param) {
        if (StringUtil.isNotEmpty(dbType)) {
            if (dbType.equals("mysql"))
                return MessageFormat.format("select distinct table_name from information_schema.columns where table_schema = {0}", (Object[]) param);
            if (dbType.equals("oracle"))
                return "select distinct colstable.table_name as  table_name from user_tab_cols colstable";
            if (dbType.equals("postgresql"))
                return "SELECT distinct c.relname AS  table_name FROM pg_class c";
            if (dbType.equals("sqlserver")) {
                return "select distinct c.name as  table_name from sys.objects c";
            }
        }
        return null;
    }

    public static String getAllCloumnSql(String dbType, String... param) {
        if (StringUtil.isNotEmpty(dbType)) {
            if (dbType.equals("mysql"))
                return MessageFormat.format("select column_name from information_schema.columns where table_name = {0} and table_schema = {1}", (Object[]) param);
            if (dbType.equals("oracle"))
                return MessageFormat.format("select column_name from all_tab_columns where table_name ={0}", (Object[]) param);
            if (dbType.equals("postgresql"))
                return MessageFormat.format("select table_name from information_schema.columns where table_name = {0}", (Object[]) param);
            if (dbType.equals("sqlserver")) {
                return MessageFormat.format("select name from syscolumns where id={0}", (Object[]) param);
            }
        }
        return null;
    }


    public static ResultMsg<Statement> isSelectSql(String sql) {
        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            if (stmt instanceof Select) {
                return ResultMsg.SUCCESS(stmt);
            }
            return ResultMsg.ERROR("该脚本不是SQL查询语句");
        } catch (Exception e) {
            String errorMsg = ExceptionUtil.getExceptionMessage(e);
            errorMsg = StringUtil.truncateStr(errorMsg, 500);
            return ResultMsg.ERROR(errorMsg);
        }
    }


    public static String getNoDataSql(String sql) {
        try {
            Select select = (Select) CCJSqlParserUtil.parse(sql);
            PlainSelect selectBody = (PlainSelect) select.getSelectBody();
            Object object = selectBody.getWhere();
            if (object == null) {
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression((Expression) new Column("1"));
                equalsTo.setRightExpression((Expression) new LongValue("1"));
                object = equalsTo;
            } else {
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression((Expression) new Column("1"));
                equalsTo.setRightExpression((Expression) new LongValue("2"));
                object = new AndExpression((Expression) object, (Expression) equalsTo);
            }
            selectBody.setWhere((Expression) object);

            return select.toString();
        } catch (Exception e) {
            log.error("sql解析异常，sql:{}, e:{}", sql, e);
            return null;
        }
    }


    public static String removeDataSql(String sql) {
        try {
            Select select = (Select) CCJSqlParserUtil.parse(sql.trim());
            PlainSelect selectBody = (PlainSelect) select.getSelectBody();
            Object object = selectBody.getWhere();
            if (object == null) {
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression((Expression) new Column("1"));
                equalsTo.setRightExpression((Expression) new LongValue("1"));
                object = equalsTo;
            } else {
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression((Expression) new Column("1"));
                equalsTo.setRightExpression((Expression) new LongValue("2"));
                object = new AndExpression((Expression) object, (Expression) equalsTo);
            }
            selectBody.setWhere((Expression) object);

            return select.toString();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("sql解析异常，sql: {}, e:{}", new Object[]{sql, e.getMessage(), e});
            return null;
        }
    }


    public static JSONObject getColumnTableRefJo(String sql) throws JSQLParserException {
        JSONArray columnTableRefJa = getColumnTableRefJa(sql);
        JSONObject jo = new JSONObject();
        for (Object object : columnTableRefJa) {
            String string = object.toString();
            String column = JSONObjectUtil.getValByKey("column", string);
            jo.put(column.toLowerCase(), object);
        }
        return jo;
    }


    public static JSONArray getColumnTableRefJa(String sql) throws JSQLParserException {
        Statement stmt = CCJSqlParserUtil.parse(sql);
        Select selectStatement = (Select) stmt;
        SelectBody selectBody = selectStatement.getSelectBody();
        JSONArray columnTableJa = new JSONArray();
        if (selectBody instanceof PlainSelect) {

            PlainSelect plainSelect = (PlainSelect) selectBody;
            List<SelectItem> selectItems = plainSelect.getSelectItems();
            Map<String, String> tableMap = new HashMap<>();
            String defaultTableName = "";
            String defaultTableAlias = "";
            if (plainSelect.getFromItem() instanceof Table) {
                Table fromItem = (Table) plainSelect.getFromItem();
                defaultTableName = fromItem.getName();
                Alias alias = fromItem.getAlias();
                if (!BeanUtils.isEmpty(alias)) {
                    defaultTableAlias = alias.getName();
                    tableMap.put(alias.getName(), fromItem.getName());
                }
                JSONObject jo = new JSONObject();
                jo.put("column", "default");
                jo.put("tableAlias", defaultTableAlias);
                jo.put("tableName", defaultTableName);
                columnTableJa.add(jo);
            }

            List<Join> joins = plainSelect.getJoins();
            if (!CollectionUtils.isEmpty(joins)) {
                for (Join join : joins) {
                    if (join.getRightItem() instanceof Table) {
                        Table rightItem = (Table) join.getRightItem();
                        tableMap.put(rightItem.getAlias().getName(), rightItem.getName());
                    }
                }
            }

            for (SelectItem selectItem : selectItems) {
                if (selectItem instanceof net.sf.jsqlparser.statement.select.AllColumns || selectItem instanceof net.sf.jsqlparser.statement.select.AllTableColumns) {
                    continue;
                }
                SelectExpressionItem item = (SelectExpressionItem) selectItem;
                Alias fieldAlias = item.getAlias();
                String expression = item.getExpression().toString();
                expression = expression.replaceAll("`", "");
                String[] split = expression.split("\\.");
                String column = "";
                String tableAlias = "";
                String tableName = "";
                if (split.length == 1) {
                    column = split[0];
                    tableAlias = defaultTableAlias;
                    tableName = defaultTableName;
                } else {
                    tableAlias = split[0];
                    column = split[1];
                    tableName = tableMap.get(tableAlias);
                }

                if (BeanUtils.isNotEmpty(fieldAlias)) {
                    column = fieldAlias.getName() + "." + column;
                } else {
                    column = column + "." + column;
                }
                column = column.replaceAll("`", "");

                JSONObject jo = new JSONObject();
                jo.put("column", column);
                jo.put("tableAlias", tableAlias);
                if (StringUtils.isEmpty(tableName)) ;


                jo.put("tableName", tableName);
                columnTableJa.add(jo);
            }
        }
        return columnTableJa;
    }

    public static String removeVarsInSql(String sql){
        // parentid = ${processid}
        int n1 = sql.indexOf("${");
        int n2 = n1;
        while(n1>0 && n2>0){
            n2 = sql.indexOf("}", n1);
            if(n2>n1){
                if(sql.substring(0, n1).endsWith("'")){
                    sql = sql.substring(0, n1)+"1"+sql.substring(n2+1);
                }else {
                    sql = sql.substring(0, n1)+"'1'"+sql.substring(n2+1);
                }
                n1 = sql.indexOf("${", n2);
            }else {
                n1 = 0;
                n2 = 0;
            }
        }
        return  sql;
    }

    public static boolean hasWhereExp(String sql) {
        try {
            //去掉变量
            sql = removeVarsInSql(sql);

            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {

                PlainSelect plainSelect = (PlainSelect) selectBody;
                return !BeanUtils.isEmpty(plainSelect.getWhere());
            }
        } catch (JSQLParserException e) {
            log.warn("判断sql是否有where条件 的sql语句有问题 : sql : {} ,e:{}", sql, e.getMessage());
            // TODO 待测试验证
            return sql.toLowerCase().indexOf("where")>1;
            //return false;
        }

        return false;
    }


    public static List<OrderByElement> getOrderByExp(String sql) {
        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {

                PlainSelect plainSelect = (PlainSelect) selectBody;
                List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
                if (orderByElements != null) {
                    return orderByElements;
                }
                // fix bug
            } else if (selectBody instanceof SetOperationList) {
                SetOperationList setOperationList = (SetOperationList) selectBody;
                List<OrderByElement> orderByElements = setOperationList.getOrderByElements();
                if (orderByElements != null) {
                    return orderByElements;
                }
            }
        } catch (JSQLParserException e) {
            log.warn("去掉order by异常 : sql : {} ,e:{}", sql, e.getMessage());
            return new ArrayList<>();
        }

        return new ArrayList<>();
    }


    public static String removeOrderByExp(String sql) {
        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {

                PlainSelect plainSelect = (PlainSelect) selectBody;
                List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
                if (orderByElements != null) {
                    plainSelect.setOrderByElements(new ArrayList());
                    return selectStatement.toString();
                }
                return sql;
            } else if (selectBody instanceof SetOperationList) {
                SetOperationList setOperationList = (SetOperationList) selectBody;
                List<OrderByElement> orderByElements = setOperationList.getOrderByElements();
                if (orderByElements != null) {
                    setOperationList.setOrderByElements(new ArrayList());
                    return selectStatement.toString();
                }
            }
        } catch (JSQLParserException e) {
            log.warn("去掉order by异常 : sql : {} ,e:{}", sql, e);
            return sql;
        }

        return sql;
    }


    public static String addOrderByExp(String sql, List<OrderByElement> orderByElements) {
        try {
            if (orderByElements == null || orderByElements.isEmpty()) {
                return sql;
            }
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) selectBody;
                plainSelect.setOrderByElements(orderByElements);
                // jsql v3.1
                return selectStatement.toString();
            } else if (selectBody instanceof SetOperationList) {
                SetOperationList setOperationList = (SetOperationList) selectBody;
                setOperationList.setOrderByElements(orderByElements);
                return setOperationList.toString();
            }
        } catch (JSQLParserException e) {
            log.error("加回order by异常 : sql : {} ,e:{}", sql, e);
            return sql;
        }

        return sql;
    }


    public static boolean hasGroupByExp(String sql) {
        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) selectBody;
                return !BeanUtils.isEmpty(plainSelect.getGroupBy());
            } else if (selectBody instanceof SetOperationList) {
                // TODO
            }
        } catch (JSQLParserException e) {
            log.error("判断sql是否有group by 条件 的sql语句有问题 : sql : {} ,e:{}", sql, e);
            return false;
        }

        return false;
    }


    public static String getGroupByExp(String sql) {
        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {

                PlainSelect plainSelect = (PlainSelect) selectBody;
                // jsql v3.1
                GroupByElement groupByElement = plainSelect.getGroupBy();
                if (groupByElement != null && !groupByElement.getGroupByExpressions().isEmpty()) {
                    String[] grp = new String[groupByElement.getGroupByExpressions().size()];
                    int i = 0;
                    for (Expression exp : groupByElement.getGroupByExpressions()) {
                        grp[i] = exp.toString();
                        i++;
                    }
                    String grpExp = StringUtils.join((Object[]) grp, " ");
                    return " group by " + grpExp.toLowerCase();
                }

//                List<Expression> groupByColumnReferences = plainSelect.getGroupByColumnReferences();
//                if (!CollectionUtils.isEmpty(groupByColumnReferences)) {
//                    String[] grp = new String[groupByColumnReferences.size()];
//                    for (int i = 0; i < groupByColumnReferences.size(); i++) {
//                        grp[i] = ((Expression) groupByColumnReferences.get(i)).toString();
//                    }
//                    String grpExp = StringUtils.join((Object[]) grp, " ");
//                    return " group by " + grpExp.toLowerCase();
//                }

                return "";
            }
        } catch (JSQLParserException e) {
            log.error("判断sql是否有group by 条件 的sql语句有问题 : sql : {} ,e:{}", sql, e);
            return "";
        }

        return "";
    }


    public static List<Expression> getGroupByExps(String sql) {
        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            SelectBody selectBody = selectStatement.getSelectBody();
            if (selectBody instanceof PlainSelect) {

                PlainSelect plainSelect = (PlainSelect) selectBody;
                GroupByElement groupByElement = plainSelect.getGroupBy();
                if (groupByElement != null && !groupByElement.getGroupByExpressions().isEmpty()) {
                    return groupByElement.getGroupByExpressions();
                }
//                List<Expression> groupByColumnReferences = plainSelect.getGroupByColumnReferences();
//                if (groupByColumnReferences != null) {
//                    return groupByColumnReferences;
//                }
            }
        } catch (JSQLParserException e) {
            log.warn("判断sql是否有group by 条件 的sql语句有问题 : sql : {} ,e:{}", sql, e.getMessage());
            return new ArrayList<>();
        }
        return new ArrayList<>();
    }


    public static JSONObject getMainTableJo(String sql) {
        JSONObject jo = new JSONObject();
        Statement stmt = null;
        try {
            stmt = CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            log.error(e.getMessage());
            return jo;
        }
        Select selectStatement = (Select) stmt;
        SelectBody selectBody = selectStatement.getSelectBody();
        if (selectBody instanceof PlainSelect) {
            PlainSelect plainSelect = (PlainSelect) selectBody;
            Table fromItem = (Table) plainSelect.getFromItem();
            Alias alias = fromItem.getAlias();
            jo.put("tableName", fromItem.getName());
            if (!BeanUtils.isEmpty(alias)) {
                jo.put("tableAlias", alias.getName());
            }
        }

        return jo;
    }

    private static List<SelectItem> getCountItem() {
        List<SelectItem> items = new ArrayList<>();

        SelectExpressionItem countItem = new SelectExpressionItem();
        setFunction(countItem);

        countItem.setAlias(new Alias("counts"));

        items.add(countItem);
        return items;
    }


    private static void setFunction(SelectExpressionItem countItem) {
        Function function = new Function();
        function.setName("COUNT");
        function.setAllColumns(true);
        countItem.setExpression((Expression) function);
    }


    public static String getSimpleCountSql(String sql) {
        return String.format("select count(*) counts from (%s) tmp", new Object[]{sql.toString()});
    }


    public static String resetQuoteStr(String str) {
        if (StringUtils.isEmpty(str)) {
            return "";
        }

        str = str.replaceAll("#___#", "'").replaceAll("#_#_#", "\"").replaceAll("#-#-#", "\\\\");
        return str;
    }

    public static String fixTwoOrderBy(String sql) {
        // 两个order by 检查
        int t1 = sql.toLowerCase().indexOf("order by");
        if (t1 > 0) {
            int t2 = sql.toLowerCase().indexOf("order by", t1 + 5);
            if (t2 > t1) {
                sql = sql.substring(0, t1) + sql.substring(t2);
                log.warn("remove order by from two");
            }
        }
        return sql;
    }

    public static String optimizeSql(String sql, String[] realSqlColumnArr) {
        try {
            if (realSqlColumnArr == null) {
                return sql;
            }

            Select select = (Select) CCJSqlParserUtil.parse(sql);

            if (!(select.getSelectBody() instanceof PlainSelect)) {
                // fix
                //sql = fixTwoOrderBy(sql);
                log.warn("only support PlainSelect, sql=\r\n" + sql);
                return sql;
            }


            String outWhere = getNoOutWhereSql(select, realSqlColumnArr);

            if (StringUtils.isEmpty(outWhere)) {
                return select.toString();
            }

            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            Limit limit = plainSelect.getLimit();
            plainSelect.setLimit(null);

            sql = select.toString();


            String realSqlColumn = StringUtils.join((Object[]) realSqlColumnArr, ",");

            String limitStr = "";
            if (limit != null) {
                limitStr = limit.toString();
            }

            String newSql = "select " + realSqlColumn + " from (" + sql + ") B where 1=1 " + outWhere + " " + limitStr;

            return newSql;
        } catch (Exception e) {
            log.warn("sql解析异常，sql:{}, e:{}", sql, e.getMessage());
            //throw new UtilException("剔除掉查询中不需要的字段异常,e:", new Object[]{ExceptionUtil.getExceptionMessage(e)});
            // jsql 3.x 不支持字段中 加减， 类似 (field1-field2) as alias
            return sql;
        }
    }


    private static String getNoOutWhereSql(Select select, String[] realSqlColumnArr) {
        List<String> realSqlColumnList = new ArrayList<>();
        for (String column : realSqlColumnArr) {


            if (column.indexOf("date_format(") >= 0) {
                column = column.split(" as ")[1];
            }
            column = column.trim();

            realSqlColumnList.add(column.replaceAll("`", ""));
        }

        if (select.getSelectBody() instanceof PlainSelect) {

        } else if (select.getSelectBody() instanceof SetOperationList) {
            SetOperationList selectBody = (SetOperationList) select.getSelectBody();

        }
        PlainSelect selectBody = (PlainSelect) select.getSelectBody();
        List<SelectItem> selectItems = selectBody.getSelectItems();
        Iterator<SelectItem> iterator = selectItems.iterator();
        List<String> subSelectItem = new ArrayList<>();

        while (iterator.hasNext()) {
            SelectItem next = iterator.next();
            if (next instanceof net.sf.jsqlparser.statement.select.AllColumns
                    || next instanceof net.sf.jsqlparser.statement.select.AllTableColumns) {
                continue;
            }
            SelectExpressionItem selectItem = (SelectExpressionItem) next;
            Expression expression = selectItem.getExpression();
            if (!(expression instanceof Column)) {
                subSelectItem.add(selectItem.getAlias().getName());
            }
        }
        iterator = selectItems.iterator();
        while (iterator.hasNext()) {
            SelectItem next = iterator.next();
            if (next instanceof net.sf.jsqlparser.statement.select.AllColumns
                    || next instanceof net.sf.jsqlparser.statement.select.AllTableColumns) {
                continue;
            }
            SelectExpressionItem selectItem = (SelectExpressionItem) next;

            removeNoNeedColumn(iterator, realSqlColumnList, selectItem);
        }


        Expression where = selectBody.getWhere();
        Map<String, Expression> outWhereMap = new HashMap<>();
        String outWhere = removeOutWhere(where, where, false, outWhereMap, subSelectItem);
        return outWhere;
    }


    private static String removeOutWhere(Expression where, Expression parentWhere, boolean isLeft, Map<String, Expression> outWhereMap, List<String> subSelectItem) {
        StringBuffer sb = new StringBuffer();
        if (where instanceof BinaryExpression) {
            BinaryExpression binary = (BinaryExpression) where;
            Expression leftExpression = binary.getLeftExpression();
            Expression rightExpression = binary.getRightExpression();
            if (leftExpression instanceof BinaryExpression) {
                sb.append(removeOutWhere(leftExpression, where, true, outWhereMap, subSelectItem));
            } else if (leftExpression instanceof Column) {
                String whereColumn = leftExpression.toString();
                whereColumn = whereColumn.replaceAll("`", "");

                if (whereColumn.indexOf(".") >= 0) {
                    whereColumn = whereColumn.split("\\.")[1];
                }
                if (subSelectItem.contains(whereColumn)) {
                    BinaryExpression parentBinary = (BinaryExpression) parentWhere;
                    Expression nullExpresssion = null;
                    EqualsTo equalsTo = new EqualsTo();
                    equalsTo.setLeftExpression((Expression) new Column("1"));
                    equalsTo.setRightExpression((Expression) new LongValue("1"));
                    EqualsTo equalsTo1 = equalsTo;
                    if (isLeft) {
                        parentBinary.setLeftExpression((Expression) equalsTo1);
                    } else {
                        parentBinary.setRightExpression((Expression) equalsTo1);
                    }
                    outWhereMap.put(whereColumn, where);
                    ((Column) binary.getLeftExpression()).setTable(null);

                    if (!isLeft) {
                        if (parentWhere instanceof AndExpression) {
                            sb.append(" AND ");
                        }else if(parentWhere instanceof OrExpression) {
                            sb.append(" OR ");
                        }
                    }

                    sb.append(binary.toString());
                }
            }

            if (rightExpression instanceof BinaryExpression) {
                sb.append(removeOutWhere(rightExpression, where, false, outWhereMap, subSelectItem));
            }else if(rightExpression instanceof Parenthesis){
                Expression expression = ((Parenthesis) rightExpression).getExpression();
                String childStr = removeOutWhere(expression, rightExpression, false, outWhereMap, subSelectItem);
                if(StringUtil.isNotEmpty(childStr)){
                    if (parentWhere instanceof AndExpression) {
                        sb.append(" AND ");
                    } else if(parentWhere instanceof OrExpression) {
                        sb.append(" OR ");
                    }
                    sb.append("(");
                    sb.append(childStr);
                    sb.append(")");
                }
            }
        }
        return sb.toString();
    }


    private static void removeNoNeedColumn(Iterator<SelectItem> iterator, List<String> realSqlColumnList, SelectExpressionItem selectItem) {
        Alias alias = selectItem.getAlias();

        if (alias != null) {
            String name = alias.getName().replaceAll("`", "");
            name = name.replaceAll("'", "");

            if (!realSqlColumnList.contains(name)) {
                iterator.remove();
            }
        } else {

            Column column = (Column) selectItem.getExpression();
            String columnName = column.getColumnName();
            columnName = columnName.replaceAll("`", "");
            boolean isInField = false;
            for (String field : realSqlColumnList) {
                if (field.equalsIgnoreCase(columnName)) {
                    isInField = true;

                    break;
                }
            }
            if (!isInField) {
                iterator.remove();
            }
        }
    }


    public static ResultMsg<String> addAliases(String sql) {
        try {
            Select select = (Select) CCJSqlParserUtil.parse(sql);
            AddAliasesVisitor instance = new AddAliasesVisitor();
            SelectBody selectBody = select.getSelectBody();
            Map<String, String> oldColumnAliasMap = handleColumnAliasMap(selectBody, null);
            selectBody.accept((SelectVisitor) instance);
            handleColumnAliasMap(selectBody, oldColumnAliasMap);
            return ResultMsg.SUCCESS(select.toString());
        } catch (Exception e) {
            log.error("sql解析异常，sql:{}, e:{}", sql, e);
            return ResultMsg.ERROR(ExceptionUtil.getRootErrorMseeage(e));
        }
    }


    public static Map<String, String> handleColumnAliasMap(SelectBody selectBody, Map<String, String> oldColumnAliasMap) {
        PlainSelect plainSelect = (PlainSelect) selectBody;
        List<SelectItem> selectItems = plainSelect.getSelectItems();

        Map<String, String> columnAliasMap = new HashMap<>();

        for (SelectItem selectItem : selectItems) {
            if (selectItem instanceof net.sf.jsqlparser.statement.select.AllColumns || selectItem instanceof net.sf.jsqlparser.statement.select.AllTableColumns) {
                continue;
            }
            SelectExpressionItem item = (SelectExpressionItem) selectItem;
            Alias fieldAlias = item.getAlias();
            String expression = item.getExpression().toString();

            String fieldAliasName = "";
            if (fieldAlias != null) {
                fieldAliasName = fieldAlias.getName();
            }

            if (oldColumnAliasMap != null) {
                String oldAlias = oldColumnAliasMap.get(expression);
                if (StringUtil.isNotEmpty(oldAlias)) {
                    fieldAlias = new Alias(oldAlias);
                } else {
                    expression = expression.replaceAll("`", "");
                    String[] split = expression.split("\\.");
                    String column = "";
                    if (split.length == 1) {
                        column = split[0];
                    } else {
                        column = split[1];
                    }
                    fieldAlias = new Alias(StringUtil.underlineToCamel(column));
                }

                item.setAlias(fieldAlias);
            }

            columnAliasMap.put(expression, fieldAliasName);
        }
        return columnAliasMap;
    }


    public static String createPageSql(String dbType, String sql, int page, int rows) {
        int beginNum = (page - 1) * rows;
        String[] sqlParam = new String[3];
        sqlParam[0] = sql;
        sqlParam[1] = beginNum + "";
        sqlParam[2] = rows + "";
        String jdbcType = dbType;
        if (jdbcType == null || "".equals(jdbcType)) {
            throw new RuntimeException("MiniDaoHandler:(数据库类型:dbType)没有设置,请检查配置文件");
        }
        if (jdbcType.indexOf("mysql") != -1) {
            sql = sql + " limit " + beginNum + "," + rows;
        } else if (jdbcType.indexOf("postgresql") != -1) {
            sql = MessageFormat.format("select * from ( {0}) sel_tab00 limit {2} offset {1}", (Object[]) sqlParam);
        } else {
            int beginIndex = (page - 1) * rows;
            int endIndex = beginIndex + rows;
            sqlParam[2] = Integer.toString(beginIndex);
            sqlParam[1] = Integer.toString(endIndex);
            if (jdbcType.indexOf("oracle") != -1) {
                sql = MessageFormat.format("select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}", (Object[]) sqlParam);
            } else if (jdbcType.indexOf("sqlserver") != -1) {
                sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
                sql = MessageFormat.format("select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}", (Object[]) sqlParam);
            }
        }
        return sql;
    }

    public static void main(String[] args){
        String sql = "select\n" +
                " where\n" +
                "  parentid = '${processid}' and parentid2 = '${processid}'";
        sql = removeVarsInSql("parentid ='${processid}'");
        System.out.println(sql);
        sql = removeVarsInSql("parentid =${processid}");
        System.out.println(sql);
        sql = removeVarsInSql("parentid =${processid} and parentid2 ='${processid}'");
        System.out.println(sql);
        sql = removeVarsInSql("parentid =${processid} and parentid2 ='${processid}' and parentid3 =${processid}");
        System.out.println(sql);
    }


}
